---
--- DROP TABLE: T027
---
DROP TABLE [T027]
GO

---
--- DROP TABLE: T026
---
DROP TABLE [dbo].[T026]
GO

---
--- DROP TABLE: T025
---
DROP TABLE [dbo].[T025]
GO

---
--- DROP TABLE: T024
---
DROP TABLE [dbo].[T024]
GO

---
--- DROP TABLE: T023
---
DROP TABLE [dbo].[T023]
GO

---
--- DROP TABLE: T022
---
DROP TABLE [dbo].[T022]
GO

---
--- DROP TABLE: T020
---
DROP TABLE [dbo].[T020]
GO

---
--- DROP TABLE: T019
---
DROP TABLE [dbo].[T019]
GO

---
--- DROP TABLE: T017
---
DROP TABLE [dbo].[T017]
GO

---
--- DROP TABLE: T015
---
DROP TABLE [dbo].[T015]
GO

---
--- DROP TABLE: T014
---
DROP TABLE [dbo].[T014]
GO

---
--- DROP TABLE: T013
---
DROP TABLE [dbo].[T013]
GO

---
--- DROP TABLE: T012
---
DROP TABLE [dbo].[T012]
GO

---
--- DROP TABLE: T004
---
DROP TABLE [dbo].[T004]
GO

---
--- DROP TABLE: T021
---
DROP TABLE [dbo].[T021]
GO

---
--- DROP TABLE: T003
---
DROP TABLE [dbo].[T003]
GO

---
--- DROP TABLE: T002
---
DROP TABLE [dbo].[T002]
GO

---
--- DROP TABLE: T001
---
DROP TABLE [dbo].[T001]
GO

---
--- DROP TABLE: T005
---
DROP TABLE [dbo].[T005]
GO

---
--- DROP TABLE: T016
---
DROP TABLE [dbo].[T016]
GO

---
--- DROP TABLE: T011
---
DROP TABLE [dbo].[T011]
GO

---
--- DROP TABLE: T010
---
DROP TABLE [dbo].[T010]
GO

---
--- DROP TABLE: T009
---
DROP TABLE [dbo].[T009]
GO

---
--- DROP TABLE: T008
---
DROP TABLE [dbo].[T008]
GO

---
--- DROP TABLE: T007
---
DROP TABLE [dbo].[T007]
GO

---
--- DROP TABLE: T006
---
DROP TABLE [dbo].[T006]
GO

---
--- CREATE TABLE: "dbo"."T006"
---
CREATE TABLE [dbo].[T006]
(
	[T006NoUnidad] varchar(15) NOT NULL,
	[T006LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T006NoUnidad])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Catalogo de Unidades
',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T006';
GO

---
--- CREATE TABLE: "dbo"."T007"
---
CREATE TABLE [dbo].[T007]
(
	[T007IdRemolque] varchar(15) NOT NULL,
	[T007LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T007IdRemolque])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Catalogo de Remolques',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T007';
GO

---
--- CREATE TABLE: "dbo"."T008"
---
CREATE TABLE [dbo].[T008]
(
	[T008IdOrigen] varchar(24) NOT NULL,
	[T008Nombre] varchar(120) NOT NULL,
	[T008OrigenDestino] char NOT NULL,
	[T008LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T008IdOrigen])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Catalogo de Origen/Destino',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T008';
GO

---
--- CREATE TABLE: "dbo"."T009"
---
CREATE TABLE [dbo].[T009]
(
	[T009IdLinea] varchar(14) NOT NULL,
	[T009Nombre] varchar(160),
	[T009LastAcess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T009IdLinea])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Catalogo de Lineas Transportistas',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T009';
GO

---
--- CREATE TABLE: "dbo"."T010"
---
CREATE TABLE [dbo].[T010]
(
	[T010NoOperador] varchar(10) NOT NULL,
	[T010Nombre] varchar(220) NOT NULL,
	[T010Sueldo] decimal(18,6) NOT NULL,
	[T010LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T010NoOperador])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Catalogo de Operadores',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T010';
GO

---
--- CREATE TABLE: "dbo"."T011"
---
CREATE TABLE [dbo].[T011]
(
	[T011Entity] int NOT NULL,
	[T011Nombre] varchar(260) NOT NULL DEFAULT ' ',
	[T011Domicilio] varchar(280) NOT NULL DEFAULT ' ',
	[T011RFC] varchar(20) NOT NULL DEFAULT ' ',
	[T011LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T011Entity])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Catalogo de Clientes',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T011';
GO

---
--- CREATE TABLE: "dbo"."T016"
---
CREATE TABLE [dbo].[T016]
(
	[T016IdLiquidacion] int NOT NULL IDENTITY,
	[T016NoLiquidacion] int NOT NULL,
	[T016TotalKms] decimal(18,6),
	[T016Sueldo] decimal(18,6),
	[T016TotalLiquidacion] decimal(18,6),
	[T016Cancelada] char NOT NULL,
	[T016Terminada] bit NOT NULL DEFAULT 0,
	[T016LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T016IdLiquidacion])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Registro de Liquidaciones x viaje',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T016';
GO

---
--- CREATE TABLE: "dbo"."T005"
---
CREATE TABLE [dbo].[T005]
(
	[T005NoViaje] int NOT NULL,
	[T016IdLiquidacion] int,
	[T005FechaInicio] datetime NOT NULL,
	[T005TipSer] char NOT NULL,
	[T005CargadoVacio] char NOT NULL,
	[T006NoUnidad] varchar(15) NOT NULL,
	[T007IdRemolque] varchar(15) NOT NULL,
	[T008IdOrigen] varchar(24) NOT NULL,
	[T008IdDestino] varchar(24) NOT NULL,
	[T009IdLinea] varchar(14) NOT NULL,
	[T010NoOperador] varchar(10) NOT NULL,
	[T011Entity] int NOT NULL,
	[T005Cancelado] char NOT NULL,
	[T005kms] decimal(18,6) NOT NULL DEFAULT 0,
	[T005LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T005NoViaje])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Tabla para registrar los datos del viaje
T005TipSer=''I'' o ''E''
T005CargadoVacio=''C'' p ''V''

',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T005';
GO


---
--- CREATE INDEX: Relation_7
---
CREATE INDEX [Relation_7] ON [dbo].[T005]
(
	[T008IdDestino]
)
GO

---
--- CREATE INDEX: Relation_8
---
CREATE INDEX [Relation_8] ON [dbo].[T005]
(
	[T016IdLiquidacion]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T005] ADD 
	FOREIGN KEY ([T006NoUnidad])
		REFERENCES [dbo].[T006]([T006NoUnidad])
		ON DELETE NO ACTION
		ON UPDATE CASCADE
GO


---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_2
---
ALTER TABLE [dbo].[T005] ADD 
	FOREIGN KEY ([T007IdRemolque])
		REFERENCES [dbo].[T007]([T007IdRemolque])
		ON DELETE NO ACTION
		ON UPDATE CASCADE
GO


---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_3
---
ALTER TABLE [dbo].[T005] ADD 
	FOREIGN KEY ([T008IdOrigen])
		REFERENCES [dbo].[T008]([T008IdOrigen])
		ON DELETE NO ACTION
		ON UPDATE CASCADE
GO


---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_4
---
ALTER TABLE [dbo].[T005] ADD 
	FOREIGN KEY ([T009IdLinea])
		REFERENCES [dbo].[T009]([T009IdLinea])
		ON DELETE NO ACTION
		ON UPDATE CASCADE
GO


---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_5
---
ALTER TABLE [dbo].[T005] ADD 
	FOREIGN KEY ([T010NoOperador])
		REFERENCES [dbo].[T010]([T010NoOperador])
		ON DELETE NO ACTION
		ON UPDATE CASCADE
GO


---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_6
---
ALTER TABLE [dbo].[T005] ADD 
	FOREIGN KEY ([T011Entity])
		REFERENCES [dbo].[T011]([T011Entity])
		ON DELETE NO ACTION
		ON UPDATE CASCADE
GO


---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_7
---
ALTER TABLE [dbo].[T005] ADD 
	FOREIGN KEY ([T008IdDestino])
		REFERENCES [dbo].[T008]([T008IdOrigen])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
GO


---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_8
---
ALTER TABLE [dbo].[T005] ADD 
	FOREIGN KEY ([T016IdLiquidacion])
		REFERENCES [dbo].[T016]([T016IdLiquidacion])
		ON DELETE SET NULL
		ON UPDATE SET DEFAULT
GO

---
--- CREATE TABLE: "dbo"."T001"
---
CREATE TABLE [dbo].[T001]
(
	[T001Name] varchar(10) NOT NULL,
	[T001Text] varchar(60) NOT NULL,
	[T001Enable] char NOT NULL DEFAULT 'S',
	[T001Visible] char NOT NULL DEFAULT 'S',
	[T001Tipo] char(2) NOT NULL,
	[T001LastAccess] timestamp NOT NULL,
	PRIMARY KEY NONCLUSTERED ([T001Name])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Tabla General de Menus
',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T001';
GO

---
--- CREATE TABLE: "dbo"."T002"
---
CREATE TABLE [dbo].[T002]
(
	[T002UserName] varchar(15) NOT NULL,
	[T002Password] varchar(12) NOT NULL,
	[T002Nombre] varchar(80) NOT NULL,
	[T002UltimoAcceso] datetime,
	[T002Correo] varchar(60),
	[T002Activo] char NOT NULL,
	[T002LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T002UserName])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Catalogo de Usuarios',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T002';
GO

---
--- CREATE TABLE: "dbo"."T003"
---
CREATE TABLE [dbo].[T003]
(
	[T003Rol] varchar(10) NOT NULL,
	[T003Descripcion] varchar(20),
	[T003LastAcess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T003Rol])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Catalogo de Roles para Usuarios, Proveedores y Clientes',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T003';
GO

---
--- CREATE TABLE: "dbo"."T021"
---
CREATE TABLE [dbo].[T021]
(
	[T021NoCartaPorte] int NOT NULL,
	[T005NoViaje] int NOT NULL,
	[T021NoCartaPorteCte] varchar(20) NOT NULL,
	[T021Flete] decimal(18,6) NOT NULL,
	[T021FleteME] decimal(18,6) NOT NULL,
	[T021Subtotal] decimal(18,6) NOT NULL,
	[T021Iva] decimal(18,6) NOT NULL,
	[T021Retencion] decimal(18,6) NOT NULL,
	[T021Total] decimal(18,6) NOT NULL,
	[T021Fecha] datetime NOT NULL,
	[T021Pedimento] varchar(20) NOT NULL,
	[T021Fianza] varchar(20) NOT NULL,
	[T021Facturada] char NOT NULL,
	[T021LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T021NoCartaPorte])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Registro de Carta Porte del Viaje',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T021';
GO


---
--- CREATE INDEX: Relation_2
---
CREATE INDEX [Relation_2] ON [dbo].[T021]
(
	[T005NoViaje]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_2
---
ALTER TABLE [dbo].[T021] ADD 
	FOREIGN KEY ([T005NoViaje])
		REFERENCES [dbo].[T005]([T005NoViaje])
		ON DELETE CASCADE
		ON UPDATE CASCADE
GO

---
--- CREATE TABLE: "dbo"."T004"
---
CREATE TABLE [dbo].[T004]
(
	[T002UserName] varchar(15) NOT NULL,
	[T001Name] varchar(10) NOT NULL,
	[T001ParentName] varchar(10),
	[T004LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T002UserName], [T001Name])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Definicion de Menus x Usuario',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T004';
GO


---
--- CREATE INDEX: T001Name_T004
---
CREATE INDEX [T001Name_T004] ON [dbo].[T004]
(
	[T001Name]
)
GO

---
--- CREATE INDEX: T001Name_T001ParentName
---
CREATE INDEX [T001Name_T001ParentName] ON [dbo].[T004]
(
	[T001ParentName]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T004] ADD 
	FOREIGN KEY ([T002UserName])
		REFERENCES [dbo].[T002]([T002UserName])
		ON DELETE CASCADE
		ON UPDATE CASCADE
GO


---
--- CREATE FOREIGN KEY CONSTRAINT: T001Name_T004
---
ALTER TABLE [dbo].[T004] ADD 
	CONSTRAINT [T001Name_T004] FOREIGN KEY ([T001Name])
		REFERENCES [dbo].[T001]([T001Name])
		ON DELETE CASCADE
		ON UPDATE CASCADE
GO


---
--- CREATE FOREIGN KEY CONSTRAINT: T001Name_T001ParentName
---
ALTER TABLE [dbo].[T004] ADD 
	CONSTRAINT [T001Name_T001ParentName] FOREIGN KEY ([T001ParentName])
		REFERENCES [dbo].[T001]([T001Name])
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
GO

---
--- CREATE TABLE: "dbo"."T012"
---
CREATE TABLE [dbo].[T012]
(
	[T005NoViaje] int NOT NULL,
	[T012Fecha] datetime NOT NULL,
	[T012Monto] decimal(18,6) NOT NULL,
	[T012Cancelado] varchar(2) NOT NULL,
	[T012LastAccess] timestamp NOT NULL,
	[T012NoAnticipo] int NOT NULL,
	PRIMARY KEY CLUSTERED ([T005NoViaje])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Registro de anticipos a los operadores x viaje',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T012';
GO


---
--- CREATE INDEX: Index_1
---
CREATE INDEX [Index_1] ON [dbo].[T012]
(
	[T012NoAnticipo]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T012] ADD 
	FOREIGN KEY ([T005NoViaje])
		REFERENCES [dbo].[T005]([T005NoViaje])
		ON DELETE CASCADE
		ON UPDATE NO ACTION
GO

---
--- CREATE TABLE: "dbo"."T013"
---
CREATE TABLE [dbo].[T013]
(
	[T013IdRol] int NOT NULL IDENTITY,
	[T011Entity] int NOT NULL,
	[T003Rol] varchar(10) NOT NULL,
	[T013LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T013IdRol])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Relacion de Clientes y sus Roles Asignados',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T013';
GO


---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T013] ADD 
	FOREIGN KEY ([T011Entity])
		REFERENCES [dbo].[T011]([T011Entity])
GO


---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_2
---
ALTER TABLE [dbo].[T013] ADD 
	FOREIGN KEY ([T003Rol])
		REFERENCES [dbo].[T003]([T003Rol])
		ON DELETE CASCADE
		ON UPDATE CASCADE
GO

---
--- CREATE TABLE: "dbo"."T014"
---
CREATE TABLE [dbo].[T014]
(
	[T014IdParametro] varchar(12) NOT NULL,
	[T014Par1] varchar(20),
	[T014Par2] varchar(20),
	[T014Par3] varchar(20),
	[T014Val1] varchar(20),
	[T014Val2] varchar(20),
	[T014Val3] varchar(20),
	[T014LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T014IdParametro])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Parametros del Sistema
',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T014';
GO

---
--- CREATE TABLE: "dbo"."T015"
---
CREATE TABLE [dbo].[T015]
(
	[T003Rol] varchar(10) NOT NULL,
	[T002UserName] varchar(15) NOT NULL,
	[T015LastAccess] timestamp NOT NULL
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Relacion de Usuarios y sus Roles asignados',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T015';
GO


---
--- CREATE INDEX: Relation_1
---
CREATE INDEX [Relation_1] ON [dbo].[T015]
(
	[T002UserName]
)
GO

---
--- CREATE INDEX: Relation_2
---
CREATE INDEX [Relation_2] ON [dbo].[T015]
(
	[T003Rol]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T015] ADD 
	FOREIGN KEY ([T002UserName])
		REFERENCES [dbo].[T002]([T002UserName])
GO


---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_2
---
ALTER TABLE [dbo].[T015] ADD 
	FOREIGN KEY ([T003Rol])
		REFERENCES [dbo].[T003]([T003Rol])
GO

---
--- CREATE TABLE: "dbo"."T017"
---
CREATE TABLE [dbo].[T017]
(
	[T017IdFactura] int NOT NULL IDENTITY,
	[T021NoCartaPorte] int NOT NULL,
	[T017NoFactura] int NOT NULL,
	[T017Cancelada] char NOT NULL,
	[T017LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T017IdFactura])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Registro de Facturas x Carta Porte',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T017';
GO


---
--- CREATE INDEX: Relation_1
---
CREATE INDEX [Relation_1] ON [dbo].[T017]
(
	[T021NoCartaPorte]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T017] ADD 
	FOREIGN KEY ([T021NoCartaPorte])
		REFERENCES [dbo].[T021]([T021NoCartaPorte])
		ON DELETE CASCADE
		ON UPDATE CASCADE
GO

---
--- CREATE TABLE: "dbo"."T019"
---
CREATE TABLE [dbo].[T019]
(
	[T003Rol] varchar(10) NOT NULL,
	[T001Name] varchar(10) NOT NULL,
	[T019LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T003Rol], [T001Name])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Relaciones de las opciones del menu y un rol de usuario',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T019';
GO


---
--- CREATE INDEX: Relation_2
---
CREATE INDEX [Relation_2] ON [dbo].[T019]
(
	[T001Name]
)
GO

---
--- CREATE INDEX: Relation_1
---
CREATE INDEX [Relation_1] ON [dbo].[T019]
(
	[T003Rol]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_2
---
ALTER TABLE [dbo].[T019] ADD 
	FOREIGN KEY ([T001Name])
		REFERENCES [dbo].[T001]([T001Name])
		ON DELETE CASCADE
		ON UPDATE CASCADE
GO


---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T019] ADD 
	FOREIGN KEY ([T003Rol])
		REFERENCES [dbo].[T003]([T003Rol])
		ON DELETE NO ACTION
GO

---
--- CREATE TABLE: "dbo"."T020"
---
CREATE TABLE [dbo].[T020]
(
	[T020Folio] int NOT NULL,
	[T005NoViaje] int NOT NULL,
	[T020FechaVale] datetime,
	[T020Litros] decimal(18,6) NOT NULL,
	[T020Monto] decimal(18,6) NOT NULL,
	[T020Cancelado] varchar(2) NOT NULL,
	[T020LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T020Folio])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Registro de vale de diesel x viaje',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T020';
GO


---
--- CREATE INDEX: Relation_1
---
CREATE INDEX [Relation_1] ON [dbo].[T020]
(
	[T005NoViaje]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T020] ADD 
	FOREIGN KEY ([T005NoViaje])
		REFERENCES [dbo].[T005]([T005NoViaje])
		ON DELETE CASCADE
		ON UPDATE CASCADE
GO

---
--- CREATE TABLE: "dbo"."T022"
---
CREATE TABLE [dbo].[T022]
(
	[T022IdComprobante] int NOT NULL IDENTITY,
	[T022Tipo] char NOT NULL,
	[T005NoViaje] int NOT NULL,
	[T022Fecha] datetime,
	[T022Descripcion] varchar(50) NOT NULL,
	[T022Monto] decimal(18,6) NOT NULL,
	[T022LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T022IdComprobante])
)
GO
EXEC sp_addextendedproperty @name = 'MS_Description', @value = 'Registro de gastos comprobados x el operador en un viaje',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'T022';
GO


---
--- CREATE INDEX: Relation_1
---
CREATE INDEX [Relation_1] ON [dbo].[T022]
(
	[T005NoViaje]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T022] ADD 
	FOREIGN KEY ([T005NoViaje])
		REFERENCES [dbo].[T005]([T005NoViaje])
		ON DELETE CASCADE
		ON UPDATE CASCADE
GO

---
--- CREATE TABLE: "dbo"."T023"
---
CREATE TABLE [dbo].[T023]
(
	[T023IdDetalle] int NOT NULL IDENTITY,
	[T021NoCartaPorte] int NOT NULL,
	[T023BultosCant] int NOT NULL,
	[T023BultosClase] varchar(20) NOT NULL,
	[T023Peso] decimal(18,6) NOT NULL,
	[T023ValorDeclarado] decimal(18,6) NOT NULL,
	[T024LastAccess] timestamp NOT NULL,
	PRIMARY KEY CLUSTERED ([T023IdDetalle])
)
GO

---
--- CREATE INDEX: Relation_1
---
CREATE INDEX [Relation_1] ON [dbo].[T023]
(
	[T021NoCartaPorte]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T023] ADD 
	FOREIGN KEY ([T021NoCartaPorte])
		REFERENCES [dbo].[T021]([T021NoCartaPorte])
		ON DELETE CASCADE
		ON UPDATE CASCADE
GO

---
--- CREATE TABLE: "dbo"."T024"
---
CREATE TABLE [dbo].[T024]
(
	[T024IdProveedor] int NOT NULL,
	[T024Nombre] varchar(360) NOT NULL,
	[T024Rfc] varchar(16),
	[T024Domicilio] varchar(400),
	PRIMARY KEY CLUSTERED ([T024IdProveedor])
)
GO
---
--- CREATE TABLE: "dbo"."T025"
---
CREATE TABLE [dbo].[T025]
(
	[T025IdFactura] int NOT NULL IDENTITY,
	[T011Entity] int,
	[T025NoFactura] varchar(32) NOT NULL,
	[T025Importe] decimal(18,6) NOT NULL,
	[T025FechaIngreso] datetime NOT NULL,
	PRIMARY KEY CLUSTERED ([T025IdFactura])
)
GO

---
--- CREATE INDEX: Relation_1
---
CREATE INDEX [Relation_1] ON [dbo].[T025]
(
	[T011Entity]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T025] ADD 
	FOREIGN KEY ([T011Entity])
		REFERENCES [dbo].[T011]([T011Entity])
GO

---
--- CREATE TABLE: "dbo"."T026"
---
CREATE TABLE [dbo].[T026]
(
	[T026IdFactura] varchar(20) NOT NULL,
	[T011Entity] int,
	[T026NoFactura] varchar(32) NOT NULL,
	[T026Importe] decimal(18,6) NOT NULL,
	[T026FechaIngreso] datetime NOT NULL,
	PRIMARY KEY CLUSTERED ([T026IdFactura])
)
GO

---
--- CREATE INDEX: Relation_1
---
CREATE INDEX [Relation_1] ON [dbo].[T026]
(
	[T011Entity]
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: Relation_1
---
ALTER TABLE [dbo].[T026] ADD 
	FOREIGN KEY ([T011Entity])
		REFERENCES [dbo].[T011]([T011Entity])
GO

---
--- CREATE TABLE: "T027"
---
CREATE TABLE [T027]
(
	[T027IdActualizacion] int NOT NULL,
	[T027IdScript] varchar(80) NOT NULL,
	[T027FechaRealizado] datetime,
	PRIMARY KEY CLUSTERED ([T027IdActualizacion])
)
GO
---
--- CREATE PROCEDURE: GetClientes
---
CREATE PROCEDURE [dbo].[GetClientes]
AS
SET NOCOUNT ON;
	SELECT T011.T011Entity,T011.T011Nombre,T011.T011RFC from T011
GO

---
--- CREATE PROCEDURE: GetDestinos
---
CREATE PROCEDURE [dbo].[GetDestinos]
AS
SET NOCOUNT ON;

	SELECT T008.T008IdOrigen,T008.T008Nombre,T008.T008OrigenDestino from T008   
WHERE T008.T008OrigenDestino in ('D','A')
GO

---
--- CREATE PROCEDURE: GetFacturas
---
CREATE PROCEDURE [dbo].[GetFacturas]
		@NoFactura as int
AS
SET NOCOUNT ON;
SELECT     dbo.T005.T005FechaInicio, dbo.T005.T006NoUnidad, dbo.T021.T021NoCartaPorte, dbo.T011.T011Nombre, dbo.T005.T007IdRemolque, 
           dbo.T008.T008Nombre AS Origen,T008_1.T008Nombre as Destino, dbo.T021.T021Subtotal
FROM       dbo.T005 
		   INNER JOIN dbo.T021 ON dbo.T005.T005NoViaje = dbo.T021.T005NoViaje 
           INNER JOIN dbo.T011 ON dbo.T005.T011Entity = dbo.T011.T011Entity 
           INNER JOIN dbo.T017 on dbo.T021.T021NoCartaPorte=dbo.T017.T021NoCartaPorte
           INNER JOIN dbo.T008 ON dbo.T005.T008IdOrigen = dbo.T008.T008IdOrigen 
           LEFT JOIN dbo.T008 AS T008_1 ON dbo.T005.T008IdDestino = T008_1.T008IdOrigen
WHERE dbo.T017.T017NoFactura =@NoFactura
GO

---
--- CREATE PROCEDURE: GetLineas
---
CREATE PROCEDURE [dbo].[GetLineas]
AS
SET NOCOUNT ON;

SELECT T009.T009IDLINEA,T009.T009NOMBRE FROM T009
GO

---
--- CREATE PROCEDURE: GetMenuOptionsFor
---
CREATE PROCEDURE [dbo].[GetMenuOptionsFor]
		@T002UserName varchar(15)
AS
	SET NOCOUNT ON;

    SELECT     T004.T001Name,T001.T001Text, T004.T001ParentName
FROM         T001 INNER JOIN
                      T004 ON T001.T001Name = T004.T001Name --anD T001.T001Name = T004.T001ParentName
                      where T004.T002UserName=@T002UserName and T001Visible='S' and T001Enable='S' and (T004.T001Name = T004.T001ParentName or  T004.T001ParentName is NULL) 
GO

---
--- CREATE PROCEDURE: GetOperadores
---
CREATE PROCEDURE [dbo].[GetOperadores]
AS
SET NOCOUNT ON;

SELECT T010NoOperador,T010Nombre,T010Sueldo
	FROM T010
GO

---
--- CREATE PROCEDURE: GetOrigenes
---
CREATE PROCEDURE [dbo].[GetOrigenes]
AS
SET NOCOUNT ON;
	SELECT T008.T008IdOrigen,T008.T008Nombre,T008.T008OrigenDestino from T008   
WHERE T008.T008OrigenDestino in ('O','A')
GO

---
--- CREATE PROCEDURE: GetRemolques
---
CREATE PROCEDURE [dbo].[GetRemolques]
AS
SET NOCOUNT ON;

SELECT T007.T007IdRemolque from T007
GO

---
--- CREATE PROCEDURE: GetSubMenuOptionsFor
---
CREATE PROCEDURE [dbo].[GetSubMenuOptionsFor]
		@T002UserName varchar(15) = '', 
		@T001ParentName varchar(10) = ''
AS
SET NOCOUNT ON;

SELECT T004.T001Name,T001.T001Text, T004.T001ParentName
FROM   T001 INNER JOIN
       T004 ON T001.T001Name = T004.T001Name --anD T001.T001Name = T004.T001ParentName
where T004.T002UserName=@T002UserName and T004.T001ParentName=@T001ParentName and T001.T001Tipo='MI' and T001Visible='S' and T001Enable='S'    	
GO

---
--- CREATE PROCEDURE: GetTransportistas
---
CREATE PROCEDURE [dbo].[GetTransportistas]
AS
SET NOCOUNT ON;
SELECT T009IdLinea,T009Nombre from T009
GO

---
--- CREATE PROCEDURE: GetUnidades
---
CREATE PROCEDURE [dbo].[GetUnidades]
AS
SET NOCOUNT ON;

SELECT T006.T006NoUnidad from T006
GO

---
--- CREATE PROCEDURE: GetViajes
---
CREATE PROCEDURE [GetViajes]
		@FechaInicio as varchar(25), 
		@FechaFinal as varchar(25)
AS
	SET NOCOUNT ON;
select a.T005NoViaje,cast(CONVERT(varchar(8), a.T005FechaInicio, 112) AS datetime) as FechaInicio,
	   TipSer = case a.T005TipSer when 'I' then 'Impo' when 'E' then 'Expo' else '?' end,
	   CargadoVacio = case a.T005CargadoVacio when 'C' then 'Cargado' when 'V' then 'Vacio' else '?' end,
	   a.T006NoUnidad,a.T007IdRemolque,b.T008Nombre as Origen ,c.T008Nombre as Destino,d.T009Nombre,
	   e.T011Nombre,f.T010Nombre,a.T005Cancelado  
from T005 a
	left join T008 b on a.t008idorigen=b.t008idorigen
	left join T008 c on a.T008IdDestino=c.T008IdOrigen  
	inner join T009 d on a.T009IdLinea =d.T009IdLinea 
	inner join T011 e on a.T011Entity = e.T011Entity 
	inner join T010 f on a.T010NoOperador = f.T010NoOperador 
	where a.T005FechaInicio between @FechaInicio and @FechaFinal 

GO

---
--- CREATE PROCEDURE: GetEntidadesByRol
---
CREATE PROCEDURE [dbo].[GetEntidadesByRol]
@Rol as varchar(10) 
AS
SET NOCOUNT ON;
	select a.T011Entity,a.T011Nombre,a.T011Domicilio,a.T011RFC,T003Rol
	from T011 a 
	inner join T013 b on a.T011Entity=b.T011Entity
	where b.T003Rol=@Rol
	order by a.T011Entity

GO

---
--- Modificacion de Vista para que incluya la Carta Porte del Cliente
---
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[FacturasView]
AS
SELECT     dbo.T005.T005FechaInicio, dbo.T005.T006NoUnidad, dbo.T021.T021NoCartaPorte,dbo.T021.T021NoCartaPorteCte, dbo.T011.T011Nombre, dbo.T005.T007IdRemolque, 
                    dbo.T008.T008Nombre AS Origen,T008_1.T008Nombre as Destino, dbo.T021.T021Subtotal
FROM         dbo.T005 
					  INNER JOIN dbo.T021 ON dbo.T005.T005NoViaje = dbo.T021.T005NoViaje 
                      INNER JOIN dbo.T011 ON dbo.T005.T011Entity = dbo.T011.T011Entity 
                      INNER JOIN dbo.T008 ON dbo.T005.T008IdOrigen = dbo.T008.T008IdOrigen 
                      LEFT JOIN dbo.T008 AS T008_1 ON dbo.T005.T008IdDestino = T008_1.T008IdOrigen
Where dbo.T021.T021Facturada='N'
GO



